
**** THIS DO FILE CLEANS DATA IN EIA861 AND GET MEAN
drop in 1/2
quietly destring var1,replace ignore (",")
quietly destring var2,replace ignore (",")
quietly gen householdper= var1/var2
sum householdper


 
 forval i=1990/1998 {
 clear
quietly import excel  "C:\Users\yqiu16\Downloads\861_`i'\f`i'\Sales_Ult_Cust_`i'.xlsx", sheet("States") firstrow
quietly drop in 1/2
quietly destring F,replace ignore (",")
quietly destring G,replace ignore (",")
quietly gen householdper= F/G
quietly drop if householdper>1000
 quietly sum householdper
quietly return list
dis `i', r(mean) 
 
 }
 
 
 forval i=1999/2000 {
 clear
quietly import excel  "C:\Users\yqiu16\Downloads\861_`i'\f`i'\Sales_Ult_Cust_`i'.xlsx", sheet("States") firstrow
quietly drop in 1/2
quietly destring G,replace ignore (",")
quietly destring H,replace ignore (",")
quietly gen householdper= G/H
quietly drop if householdper>1000
 quietly sum householdper
quietly return list
dis `i', r(mean) 
 
 }
 
 
  forval i=2001/2011 {
 clear
quietly import excel  "C:\Users\yqiu16\Downloads\861_`i'\f`i'\Sales_Ult_Cust_`i'.xlsx", sheet("States") firstrow
quietly drop in 1/2
quietly destring J,replace ignore (",")
quietly destring K,replace ignore (",")
quietly gen householdper= J/K
quietly drop if householdper>1000
 quietly sum householdper
quietly return list
dis `i', r(mean) 
 
 }
 
 
   forval i=2012/2012 {
 clear
quietly import excel  "C:\Users\yqiu16\Downloads\f861`i'\Sales_Ult_Cust_`i'.xlsx", sheet("States") firstrow
quietly drop in 1/2
quietly destring J,replace ignore (",")
quietly destring K,replace ignore (",")
quietly gen householdper= J/K
quietly drop if householdper>1000
 quietly sum householdper
quietly return list
dis `i', r(mean) 
 
 }
 
    forval i=2013/2018 {
 clear
quietly import excel  "C:\Users\yqiu16\Downloads\f861`i'\Sales_Ult_Cust_`i'.xlsx", sheet("States") firstrow
quietly drop in 1/2
quietly destring K,replace ignore (",")
quietly destring L,replace ignore (",")
quietly gen householdper= K/L
quietly drop if householdper>1000
 quietly sum householdper
quietly return list
dis `i', r(mean) 
 
 }
 
 
     forval i=2019/2019 {
 clear
quietly import excel  "C:\Users\yqiu16\Downloads\f861`i'\Sales_Ult_Cust_`i'.xlsx", sheet("States") firstrow
quietly drop in 1/2
quietly destring L,replace ignore (",")
quietly destring M,replace ignore (",")
quietly gen householdper= L/M
 quietly sum householdper
quietly return list
dis `i', r(mean) 
 
 }
 
      forval i=2020/2020 {
 clear
quietly import excel  "C:\Users\yqiu16\Downloads\f861`i'\Sales_Ult_Cust_`i'.xlsx", sheet("States") firstrow
quietly drop in 1/2
quietly destring K,replace ignore (",")
quietly destring L,replace ignore (",")
quietly gen householdper= K/L
 quietly sum householdper
quietly return list
dis `i', r(mean) 
 
 }
 
 

 